/**************************************************************************
	Replication do-file: "Corruption in Customs"
	Cyril Chalendard, Ana Fernandes, Gael Raballand and Bob Rijkers
	
	Created on: 01/07/2022
**************************************************************************/

clear all
set more off, perm
cap log close
cls

* ----------------------- DIRECTORIES AND FOLDERS ----------------------- *

* Directories in which data are stored
global main "PUT YOUR DIRECTORY PATH HERE"
cd "$folder"

* Folders
global outputdata = "$main\Output Data"
cap mkdir "$main\Tables"
global tables = "$main\Tables"

* ----------------------------- BEGINS HERE ----------------------------- *

* -------
* Dataset
* -------
use "$outputdata\CFRR.dta", clear // open dataset

* Sample
keep if sample_int==1 & eis_f!=. // define sample


* --------
* Table A2
* --------

* Matrix
forvalues i = 1(2)3 {
	mat tableA2_`i' = J(11,1,.)
}
forvalues i = 2(2)4 {
	mat tableA2_`i' = J(11,1,.)
}

* Unique brokers by importer
preserve
sort key_importer key_dec // sort observations
duplicates drop key_importer key_dec, force // drop duplicates
gen n = 1 // create a constant for future count
egen nbrokers = sum(n) , by(key_importer) // create number of brokers by importer
bys key_importer: gen count = _n // create number of importers
** Totals
qui: tab nbrokers if count==1, matcell(byimporter) // tabulate number of importers
mat tableA2_1[1,1] = byimporter
mat tableA2_1[11,1] = `r(N)'
** Percentages
forvalues i = 1(1)10 {
	mat tableA2_2[`i',1] = (tableA2_1[`i',1]/`r(N)')*100
}
mat tableA2_2[11,1] = (`r(N)'/`r(N)')*100
restore

* Unique brokers by importer-semester
preserve
sort key_importer key_dec nc_sem // sort observations
duplicates drop key_importer key_dec nc_sem, force // drop duplicates
gen n = 1 // create a constant for future count
egen nbrokers = sum(n), by(ms) // create number of brokers by importer-semester
bys ms: gen count = _n // create number of importer-semesters
** Totals
qui: tab nbrokers if count==1, matcell(byimportersemester) // tabulate number of importer-semester pairs
mat tableA2_3[1,1] = byimportersemester
mat tableA2_3[11,1] = `r(N)'
** Percentages
forvalues i = 1(1)10 {
	mat tableA2_4[`i',1] = (tableA2_3[`i',1]/`r(N)')*100
}
mat tableA2_4[11,1] = (`r(N)'/`r(N)')*100
restore


* Export
* ------
putexcel set "$tables\Table A2.xlsx", replace sheet(TableA2) // create a new excel spreadsheet

* Secondary titles
putexcel A2 = "Number of brokers", left vcenter bold
putexcel (B1:C1), merge hcenter vcenter bold
putexcel B1 = "By importer"
putexcel (D1:E1), merge hcenter vcenter bold
putexcel D1 = "By importer-semester"
local cells `" "B" "C" "D" "E" "'
local labels `" "N" "%" "N" "%" "'
forvalues i = 1(1)4 {

	local x: word `i' of `labels'
	local y: word `i' of `cells'

	putexcel `y'2 = "`x'", hcenter vcenter
}

* Counts
local j = 3
forvalues i = 1(1)10 {

	putexcel A`j' = "`i'", left vcenter
	local j = `j'+1
}
putexcel A13 = "Total", left vcenter

* Coefficients
local cells `" "B" "C" "D" "E" "'
forvalues i = 1(1)4 {

	local x: word `i' of `cells'

	if "`i'" == "1" | "`i'" == "3" {
		putexcel `x'3 = matrix(tableA2_`i'), nformat(number_sep) hcenter vcenter
	}

	else {
		putexcel `x'3 = matrix(tableA2_`i'), nformat(0.00) hcenter vcenter
	}
}


* -------------------------------- ENDS HERE -------------------------------- *